"""
Case Type   : DBlink功能
Case Name   : 连接目标库白名单配置连接库校验
Create At   : 2024/4/18
Owner       : peilinqian
Description :
    step1: dblink端系统用户进行dblink创建;
    step2: 目标端创建用户;
    step3: 目标库进行pg_hba白名单配置，进行初始用户dblink_connect;
    step4: 目标库进行pg_hba白名单配置，进行非目标用户dblink_connect;
    step5: 目标库进行pg_hba白名单配置，进行目标用户dblink_connect，密码不正确;
    step6: 目标库进行pg_hba白名单配置，进行目标用户dblink_connect，密码正确;
Expect      :
    step1: dblink端系统用户进行dblink创建; expect:创建成功
    step2: 目标端创建用户; expect:成功
    step3: 目标库进行pg_hba白名单配置，进行初始用户dblink_connect; expect:失败
    step4: 目标库进行pg_hba白名单配置，进行非目标用户dblink_connect; expect:失败
    step5: 目标库进行pg_hba白名单配置，进行目标用户dblink_connect，密码不正确; expect:失败
    step6: 目标库进行pg_hba白名单配置，进行目标用户dblink_connect，密码正确; expect:成功
History     :
    Modified by peilinqian 2024-4-29: 优化跳过执行提示信息，优化步骤描述；；
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

com = Common()


@unittest.skipIf(not com.check_node_exists('remote1_PrimaryDbuser'),
                 '无远程主机环境不执行')
class DblinkCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:初始化----')
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_dblink_0004'
        self.u_sys = 'u_dblink_sys0004'
        self.connect_sys = f'-U {self.u_sys} -W {macro.PASSWD_INITIAL}'
        self.remote_db = Node(node='remote1_PrimaryDbuser')
        self.remote_sh = CommonSH('remote1_PrimaryDbuser')
        self.err_flag1 = 'FATAL:  Forbid remote connection with initial user'
        self.err_flag2 = 'FATAL:  no pg_hba.conf entry for host ".*", ' \
                         'user ".*", database ".*"'
        self.err_flag3 = 'FATAL:  Invalid username/password,login denied'

    def test_main(self):
        step_txt = '----step1:dblink端创建用户; expect:成功----'
        self.log.info(step_txt)
        user_sql = f"drop user if exists {self.u_sys} cascade;" \
                   f"create user {self.u_sys} sysadmin " \
                   f"password '{macro.PASSWD_INITIAL}';"
        result = self.pri_sh.execut_db_sql(user_sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step1:dblink端创建数据库; expect:成功----'
        self.log.info(step_txt)
        db_sql = f"drop database if exists {self.db_name};" \
                 f"create database {self.db_name}"
        result = self.pri_sh.execut_db_sql(db_sql)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, result,
                      "执行失败" + step_txt)
        step_txt = '----step1: dblink端系统用户进行dblink创建; expect:创建成功----'
        self.log.info(step_txt)
        create_sql = f"create extension dblink;"
        result = self.pri_sh.execut_db_sql(create_sql,
                                           sql_type=self.connect_sys,
                                           dbname=self.db_name)
        self.log.info(result)
        self.assertIn(self.constant.create_extension_success, result,
                      "执行失败" + step_txt)

        step_txt = '----step2: 目标端创建用户; expect:成功----'
        self.log.info(step_txt)
        msg = self.remote_sh.execut_db_sql(user_sql,
                                           env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, msg,
                      "执行失败" + step_txt)

        step_txt = '----step3: 目标库进行pg_hba白名单配置，进行初始用户dblink_connect; ' \
                   'expect:失败----'
        self.log.info(step_txt)
        self.log.info('----dblink端pg_hba----')
        guc_res = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.remote_db.db_name} {self.remote_db.ssh_user} '
            f'{self.pri_dbuser.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, "执行失败" + step_txt)
        self.log.info('----进行dblink_connect----')
        con_sql = f"select dblink_connect('host={self.remote_db.db_host} " \
                  f"port={self.remote_db.db_port} " \
                  f"dbname={self.remote_db.db_name} " \
                  f"user={self.remote_db.ssh_user} " \
                  f"password={self.remote_db.db_password}')"
        result = self.pri_sh.execut_db_sql(con_sql,
                                           sql_type=self.connect_sys,
                                           dbname=self.db_name)
        self.log.info(result)
        self.assertIn(self.err_flag1, result, '执行失败:' + step_txt)

        step_txt = '----step4: 目标库进行pg_hba白名单配置，进行非目标用户dblink_connect; ' \
                   'expect:失败----'
        self.log.info(step_txt)
        self.log.info('----dblink端pg_hba----')
        guc_res = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.remote_db.db_name} {self.u_sys} '
            f'{self.pri_dbuser.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, "执行失败" + step_txt)
        self.log.info('----进行dblink_connect----')
        con_sql = f"select dblink_connect('host={self.remote_db.db_host} " \
                  f"port={self.remote_db.db_port} " \
                  f"dbname={self.remote_db.db_name} " \
                  f"user={self.remote_db.db_user} " \
                  f"password={self.remote_db.db_password}')"
        result = self.pri_sh.execut_db_sql(con_sql,
                                           sql_type=self.connect_sys,
                                           dbname=self.db_name)
        self.log.info(result)
        self.assertRegex(result, self.err_flag2, '执行失败:' + step_txt)

        step_txt = '----step5: 目标库进行pg_hba白名单配置，进行目标用户dblink_connect，密码不正确; ' \
                   'expect:失败----'
        self.log.info(step_txt)
        self.log.info('----进行dblink_connect----')
        con_sql = f"select dblink_connect('host={self.remote_db.db_host} " \
                  f"port={self.remote_db.db_port} " \
                  f"dbname={self.remote_db.db_name} " \
                  f"user={self.u_sys} " \
                  f"password={self.remote_db.db_password}')"
        result = self.pri_sh.execut_db_sql(con_sql,
                                           sql_type=self.connect_sys,
                                           dbname=self.db_name)
        self.log.info(result)
        self.assertIn(self.err_flag3, result, '执行失败:' + step_txt)

        step_txt = '----step6: 目标库进行pg_hba白名单配置，进行目标用户dblink_connect，密码正确; ' \
                   'expect:成功----'
        self.log.info(step_txt)
        self.log.info('----进行dblink_connect----')
        con_sql = f"select dblink_connect('host={self.remote_db.db_host} " \
                  f"port={self.remote_db.db_port} " \
                  f"dbname={self.remote_db.db_name} " \
                  f"user={self.u_sys} " \
                  f"password={macro.PASSWD_INITIAL}')"
        result = self.pri_sh.execut_db_sql(con_sql,
                                           sql_type=self.connect_sys,
                                           dbname=self.db_name)
        self.log.info(result)
        self.assertEqual('OK', result.splitlines()[-2].strip(),
                         "执行失败" + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step7_txt = '----step7:清理数据; expect:成功----'
        self.log.info(step7_txt)
        drop_sql = f"drop database {self.db_name};" \
                   f"drop user if exists {self.u_sys} cascade;"
        msg1 = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(msg1)
        step8_txt = '----step8:恢复目标端pg_hba配置及删除用户; expect:成功----'
        self.log.info(step8_txt)
        guc_res1 = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.remote_db.db_name} {self.remote_db.ssh_user} '
            f'{self.pri_dbuser.db_host}/32',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res1)
        guc_res2 = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.remote_db.db_name} {self.u_sys} '
            f'{self.pri_dbuser.db_host}/32',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res2)
        msg2 = self.remote_sh.execut_db_sql(drop_sql,
                                            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(msg2)
        self.log.info(f'----{os.path.basename(__file__)}:执行完毕----')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, msg1,
                      "执行失败" + step7_txt)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, msg1,
                      "执行失败" + step7_txt)
        self.assertTrue(guc_res1, "执行失败" + step8_txt)
        self.assertTrue(guc_res2, "执行失败" + step8_txt)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, msg2,
                      "执行失败" + step8_txt)
